package dao;

import entity.Flower;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import DBU.DBUtil;
public class FlowerDAOImp implements FlowerDAO{

    @Override
    public List<Flower> addFlower(Flower flower) {
        Connection conn = DBUtil.getConnection();
        String sql = "insert into flower values(?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, flower.getFid());
            ps.setString(2,flower.getVariety());
            ps.setString(3,flower.getDetails());
            ps.setDouble(4,flower.getPrice());
            ps.setInt(5,flower.getStore());
            ps.executeUpdate();
            return (List<Flower>) ps.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            DBUtil.close(conn, null, ps, null);
        }
        return null;
    }

    @Override
    public List<Flower> checkFlower() {
        Connection conn = DBUtil.getConnection();
        String sql = "select * from flower";
        PreparedStatement ps = null;
        try {
            ps  = conn.prepareStatement(sql);
            ps.executeQuery();
            return (List<Flower>) ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Flower> checkFlowerByVariety(String variety) {
        Connection conn = DBUtil.getConnection();
        String sql = "select * from flower where variety = '"+variety+"'";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.executeQuery();
            return (List<Flower>) ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int inFlower(String vartety,int num) {
        Connection conn = DBUtil.getConnection();
        //找到对应鲜花，更新库存
        String sql ="UPDATE flower set store=store+"+num+" WHERE variety='"+vartety+"'";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            return num;  //入库成功
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;     //返回到逻辑业务层，-1表示入库失败
    }

    @Override
    public int outFlower(String vartety,int num) {
        Connection conn = DBUtil.getConnection();
        //找到对应鲜花，更新库存
        String sql ="UPDATE flower set store=store-"+num+" WHERE variety='"+vartety+"'";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            return num;  //入库成功
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;     //返回到逻辑业务层，-1表示出库失败
    }

    @Override
    public List<String> buyFlower(int fid, int num) {
        List<String> list = new ArrayList<String>();   //返回的结果
        int id = 0;
        String variety = "";
        double price = 0;
        Connection conn = DBUtil.getConnection();
        String checksql = "select * from flower where fid = "+fid;  //查询
        String updatesql = "update flower set store = store - "+num+" where fid ="+fid;     //更新库存
        ResultSet rs = null;
        try {
            PreparedStatement ps1 = conn.prepareStatement(checksql);
            PreparedStatement ps2 = conn.prepareStatement(updatesql);
            rs = ps1.executeQuery();
            ps2.executeQuery();
            while (rs.next()){
                id = rs.getInt("fid");
                variety = rs.getString("variety");
                price = rs.getDouble("price");
            }
            double sum = price*num;   //计算总价
            list.add(String.valueOf(id));
            list.add(variety);
            list.add(String.valueOf(sum));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;     //返回鲜花编号、品种、总价
    }
}
